#---------------------------------
from sqlalchemy import create_engine,types,text,inspect
import  pandas as pd
import  time
from datetime import datetime,timedelta
from qmt_tool.confile_tool import get_conf_database_file
class DbTool:
    def __init__(self):
#一个库是存结果，一个是存数据
        dbdict=get_conf_database_file()

        self.quant_result_url = "mysql+pymysql://"+dbdict["star_user"]+":"+dbdict['star_password']+"@"+dbdict['star_host']+"/quant_result"


        self.quant_url = "mysql+pymysql://"+dbdict["star_user"]+":"+dbdict['star_password']+"@"+dbdict['star_host']+"/quant"
        # self.dbengine= self.get_dbengine_quant()
        # self.dbengine_result= self.get_dbengine_quant_result()
    def get_dbengine_quant_result(self):
        connect_info = self.quant_result_url
        engine = create_engine(connect_info)  # use sqlalchemy to build link-engine
        # print(engine, 'mysql链接初始化成功')
        return engine

    def get_dbengine_quant(self):
        connect_info = self.quant_url
        engine = create_engine(connect_info)  # use sqlalchemy to build link-engine
        # print(engine, 'mysql链接初始化成功')
        return engine



    def save_to_mysql_append(sefl,df, engine, table_name,table_columns=''):

            # df.head(0).to_sql(table_name, engine, index=False, dtype=table_columns)
        df.to_sql(table_name, engine, if_exists='append', index=False,chunksize=9999)

    def save_to_mysql_replace(self,df, engine, table_name,table_columns=''):

        sql_comm = "truncate table  " + table_name
        with engine.connect() as conn:
            with conn.begin():  # Optional: start a transaction
                conn.execute(text(sql_comm))
            conn.commit()

        # query_res = engine.
        time.sleep(0.5)
        df.to_sql(table_name, engine, if_exists='append', index=False,chunksize=9999)


    def execute_sql(self,engine, sql_comm):
        with engine.connect() as conn:
            with conn.begin():  # Optional: start a transaction
                conn.execute(text(sql_comm))


    def get_alldata_fromdb(self,engine,table_name):
        sql_comm = "SELECT * FROM " + table_name
        try:
            data = pd.read_sql(text(sql_comm), engine)
            return data
        except Exception as e:
            print('读取数据出错', e)

    def get_stock_list_fromdb(self):
        # -----查询当前所有正常上市交易的股票列表,不要北交所的---------
        dbengine=self.get_dbengine_quant()
        sql_comm = "SELECT symbol FROM ak_stock_list "
        try:
            data = pd.read_sql(text(sql_comm), dbengine)
            stock_pool = data['symbol'].tolist()
            return stock_pool
        except Exception as e:
            print('读取数据出错', e)
        # 设定需要获取数据的股票池

    def get_myetf_pool(self):
        sql_comm = "SELECT symbol,name FROM my_index_fund_list order by order_num asc"
        dbengine = self.get_dbengine_quant()
        try:
            df = pd.read_sql(text(sql_comm), dbengine)
            hangye_list = df['symbol'].tolist()
            return hangye_list, df
        except Exception as e:
            print('读取数据出错', e)

    def get_myetf_daily_someday(self, symbol, start_date=(datetime.today() - timedelta(days=120)).strftime('%Y%m%d'),
                                end_date='20500101'):
        dbengine = self.get_dbengine_quant()
        sql_comm = "select symbol,trade_date,name,open,high,low,close,volume,amount,zhangdiefu from my_index_fund_daily_em where symbol = :symbol and trade_date>:start_date and trade_date <= :end_date order by trade_date asc"
        try:
            df = pd.read_sql(text(sql_comm), dbengine,
                             params={"symbol": symbol, "start_date": start_date, 'end_date': end_date})
            df.index = df['trade_date']
            return df
        except Exception as e:
            print('读取数据出错', e)
